package com.campus.server.dao;

import com.campus.common.util.DBUtil;
import com.campus.common.vo.*;

import java.sql.*;
import java.util.*;

public class CourseDao {

    // -------------------------------
    // 1️⃣ 获取所有课程及其班级
    // -------------------------------
    public List<Course> getAllCourses() {
        Map<String, Course> courseMap = new LinkedHashMap<>();
        String sql = "SELECT c.CourseID, c.CourseName, c.Credit, c.Department, " +
                "cl.ClassID, cl.TeacherID, cl.CourseTime, cl.CourseLocation, "
                + "cl.Capacity, cl.SelectedCount, cl.ClassType, "
                + "t.teacherId, t.name, t.sex, t.age, t.department, t.contactInfo "
                + "FROM course c "
                + "LEFT JOIN classroom cl ON c.CourseID = cl.CourseID "
                + "LEFT JOIN teacher1 t ON cl.TeacherID = t.teacherId "
                + "ORDER BY c.CourseID, cl.ClassID";

        try (Connection conn = DBUtil.getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            while (rs.next()) {
                String courseId = rs.getString("CourseID");
                Course course = courseMap.get(courseId);
                if (course == null) {
                    course = new Course(courseId, rs.getString("CourseName"), rs.getInt("Credit"), rs.getString("Department"));
                    courseMap.put(courseId, course);
                }

                String classId = rs.getString("ClassID");
                if (classId != null) {
                    Classroom classroom = new Classroom();
                    classroom.setClassId(classId);
                    classroom.setCourseId(courseId);
                    classroom.setCourseTime(rs.getString("CourseTime"));
                    classroom.setCourseLocation(rs.getString("CourseLocation"));
                    classroom.setCapacity(rs.getInt("Capacity"));
                    classroom.setSelectedCount(rs.getInt("SelectedCount"));
                    classroom.setClassType(rs.getString("ClassType"));  // ✅ 设置班级性质

                    Teacher teacher = new Teacher();
                    teacher.setTeacherId(rs.getString("teacherId"));
                    teacher.setName(rs.getString("name"));
                    teacher.setSex(rs.getString("sex"));
                    teacher.setAge(rs.getInt("age"));
                    teacher.setDepartment(rs.getString("department"));
                    teacher.setContactInfo(rs.getString("contactInfo"));

                    classroom.setTeacher(teacher);
                    course.addClassroom(classroom);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return new ArrayList<>(courseMap.values());
    }


    // -------------------------------
// 2️⃣ 获取学生已选课程及班级（新增 ClassType）
// -------------------------------
    public List<Course> getSelectedCourses(String studentId) {
        Map<String, Course> courseMap = new LinkedHashMap<>();
        String sql = "SELECT c.CourseID, c.CourseName, c.Credit, c.Department, " + // ✅
                "cl.ClassID, cl.TeacherID, cl.CourseTime, cl.CourseLocation, cl.Capacity, cl.SelectedCount, cl.ClassType, "
                + "t.teacherId, t.name, t.sex, t.age, t.department, t.contactInfo "
                + "FROM student_course sc "
                + "INNER JOIN classroom cl ON sc.ClassID = cl.ClassID "
                + "LEFT JOIN course c ON cl.CourseID = c.CourseID "
                + "LEFT JOIN teacher1 t ON cl.TeacherID = t.teacherId "
                + "WHERE sc.StudentID=? "
                + "ORDER BY c.CourseID, cl.ClassID";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, studentId);
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    String courseId = rs.getString("CourseID");
                    Course course = courseMap.get(courseId);
                    if (course == null) {
                        course = new Course(courseId, rs.getString("CourseName"), rs.getInt("Credit"), rs.getString("Department"));
                        courseMap.put(courseId, course);
                    }

                    String classId = rs.getString("ClassID");
                    if (classId != null) {
                        Classroom classroom = new Classroom();
                        classroom.setClassId(classId);
                        classroom.setCourseId(courseId);
                        classroom.setCourseTime(rs.getString("CourseTime"));
                        classroom.setCourseLocation(rs.getString("CourseLocation"));
                        classroom.setCapacity(rs.getInt("Capacity"));
                        classroom.setSelectedCount(rs.getInt("SelectedCount"));
                        classroom.setClassType(rs.getString("ClassType")); // ✅ 设置 ClassType

                        Teacher teacher = new Teacher();
                        teacher.setTeacherId(rs.getString("TeacherID")); // ⚠️ 注意字段名大小写要和 SQL 一致
                        teacher.setName(rs.getString("name"));
                        teacher.setSex(rs.getString("sex"));
                        teacher.setAge(rs.getInt("age"));
                        teacher.setDepartment(rs.getString("department"));
                        teacher.setContactInfo(rs.getString("contactInfo"));
                        classroom.setTeacher(teacher);

                        course.addClassroom(classroom);
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return new ArrayList<>(courseMap.values());
    }


    // -------------------------------
// 3️⃣ 根据班级ID获取班级（选课/退课）
// -------------------------------
    public Classroom getClassById(String classId) {
        String sql = "SELECT cl.ClassID, cl.CourseID, cl.TeacherID, cl.CourseTime, cl.CourseLocation, cl.Capacity, cl.SelectedCount, cl.ClassType, " + // ✅ 加入 ClassType
                "t.name, t.sex, t.age, t.department, t.contactInfo " +
                "FROM classroom cl " +
                "LEFT JOIN teacher1 t ON cl.TeacherID = t.teacherId " +
                "WHERE cl.ClassID=?";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, classId);
            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    Classroom classroom = new Classroom();
                    classroom.setClassId(rs.getString("ClassID"));
                    classroom.setCourseId(rs.getString("CourseID"));
                    classroom.setCourseTime(rs.getString("CourseTime"));
                    classroom.setCourseLocation(rs.getString("CourseLocation"));
                    classroom.setCapacity(rs.getInt("Capacity"));
                    classroom.setSelectedCount(rs.getInt("SelectedCount"));
                    classroom.setClassType(rs.getString("ClassType")); // ✅ 设置 ClassType

                    Teacher teacher = new Teacher();
                    teacher.setTeacherId(rs.getString("TeacherID")); // ⚠️ 注意字段名大小写一致
                    teacher.setName(rs.getString("name"));
                    teacher.setSex(rs.getString("sex"));
                    teacher.setAge(rs.getInt("age"));
                    teacher.setDepartment(rs.getString("department"));
                    teacher.setContactInfo(rs.getString("contactInfo"));
                    classroom.setTeacher(teacher);

                    return classroom;
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }


    // -------------------------------
    // 4️⃣ 查询课程（模糊搜索）
    // -------------------------------


    public List<Course> queryCourse(String keyword) {
        Map<String, Course> courseMap = new LinkedHashMap<>();
        if (keyword == null || keyword.trim().isEmpty()) return new ArrayList<>();
        String like = "%" + keyword.trim() + "%";

        String sql = "SELECT c.CourseID, c.CourseName, c.Credit, c.Department, " +
                "cl.ClassID, cl.TeacherID, cl.CourseTime, cl.CourseLocation, cl.Capacity, cl.SelectedCount, cl.ClassType, " +
                "t.teacherId, t.name, t.sex, t.age, t.department, t.contactInfo " +
                "FROM course c " +
                "LEFT JOIN classroom cl ON c.CourseID = cl.CourseID " +
                "LEFT JOIN teacher1 t ON cl.TeacherID = t.teacherId " +
                "WHERE c.CourseID LIKE ? OR c.CourseName LIKE ? " +
                "ORDER BY c.CourseID, cl.ClassID";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {

            ps.setString(1, like); // 模糊匹配 CourseID
            ps.setString(2, like); // 模糊匹配 CourseName

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    String cId = rs.getString("CourseID");
                    Course course = courseMap.get(cId);
                    if (course == null) {
                        course = new Course(cId, rs.getString("CourseName"), rs.getInt("Credit"), rs.getString("Department"));
                        courseMap.put(cId, course);
                    }

                    String classId = rs.getString("ClassID");
                    if (classId != null) {
                        Classroom classroom = new Classroom();
                        classroom.setClassId(classId);
                        classroom.setCourseId(cId);
                        classroom.setCourseTime(rs.getString("CourseTime"));
                        classroom.setCourseLocation(rs.getString("CourseLocation"));
                        classroom.setCapacity(rs.getInt("Capacity"));
                        classroom.setSelectedCount(rs.getInt("SelectedCount"));
                        classroom.setClassType(rs.getString("ClassType"));

                        Teacher teacher = new Teacher();
                        teacher.setTeacherId(rs.getString("teacherId"));
                        teacher.setName(rs.getString("name"));
                        teacher.setSex(rs.getString("sex"));
                        teacher.setAge(rs.getInt("age"));
                        teacher.setDepartment(rs.getString("department"));
                        teacher.setContactInfo(rs.getString("contactInfo"));
                        classroom.setTeacher(teacher);

                        course.addClassroom(classroom);
                    }
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return new ArrayList<>(courseMap.values());
    }


    // -------------------------------
    // 5️⃣ 学生选课 / 退课
    // -------------------------------
    // -------------------------------
    public boolean deleteStudentClassroom(String studentId, String classId) {
        String deleteSql = "DELETE FROM student_course WHERE StudentID=? AND ClassID=?";
        String updateCountSql = "UPDATE classroom SET SelectedCount = SelectedCount - 1 WHERE ClassID=? AND SelectedCount > 0";

        Connection conn = null;
        try {
            conn = DBUtil.getConnection();
            conn.setAutoCommit(false); // 开启事务

            // 1️⃣ 删除 student_course
            try (PreparedStatement ps = conn.prepareStatement(deleteSql)) {
                ps.setString(1, studentId);
                ps.setString(2, classId);
                int rows = ps.executeUpdate();
                if (rows == 0) {
                    System.out.println("未找到该学生选课记录: " + studentId + ", " + classId);
                    conn.rollback();
                    return false;
                }
            }

            // 2️⃣ 更新 SelectedCount
            try (PreparedStatement ps = conn.prepareStatement(updateCountSql)) {
                ps.setString(1, classId);
                ps.executeUpdate();
            }

            conn.commit(); // 提交事务
            return true;

        } catch (SQLException e) {
            e.printStackTrace();
            try {
                if (conn != null) conn.rollback(); // 正确回滚
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        } finally {
            try {
                if (conn != null) conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return false;
    }


    public boolean hasSelected(String studentId, String courseId) {
        String sql = "SELECT COUNT(*) FROM student_course sc " +
                "INNER JOIN classroom cl ON sc.ClassID = cl.ClassID " +
                "WHERE sc.StudentID=? AND cl.CourseID=?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, studentId);
            ps.setString(2, courseId);
            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) return rs.getInt(1) > 0;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    public List<Classroom> getSelectedClassroomsFlat(String studentId) {
        List<Classroom> list = new ArrayList<>();
        String sql = "SELECT cl.ClassID, cl.CourseID, cl.TeacherID, cl.CourseTime, cl.CourseLocation, cl.Capacity, cl.SelectedCount, cl.ClassType, " + // ✅ 加 ClassType
                "t.name, t.sex, t.age, t.department, t.contactInfo " +
                "FROM student_course sc " +
                "INNER JOIN classroom cl ON sc.ClassID = cl.ClassID " +
                "LEFT JOIN teacher1 t ON cl.TeacherID = t.teacherId " +
                "WHERE sc.StudentID=?";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, studentId);
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    Classroom classroom = new Classroom();
                    classroom.setClassId(rs.getString("ClassID"));
                    classroom.setCourseId(rs.getString("CourseID"));
                    classroom.setCourseTime(rs.getString("CourseTime"));
                    classroom.setCourseLocation(rs.getString("CourseLocation"));
                    classroom.setCapacity(rs.getInt("Capacity"));
                    classroom.setSelectedCount(rs.getInt("SelectedCount"));
                    classroom.setClassType(rs.getString("ClassType")); // ✅ 设置 ClassType

                    Teacher teacher = new Teacher();
                    teacher.setTeacherId(rs.getString("teacherId"));
                    teacher.setName(rs.getString("name"));
                    teacher.setSex(rs.getString("sex"));
                    teacher.setAge(rs.getInt("age"));
                    teacher.setDepartment(rs.getString("department"));
                    teacher.setContactInfo(rs.getString("contactInfo"));
                    classroom.setTeacher(teacher);

                    list.add(classroom);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }


    // -------------------------------
    // 6️⃣ 根据班级ID获取学生名单
    // -------------------------------
    public List<Student> getStudentsByClassId(String classId) {
        List<Student> students = new ArrayList<>();
        String sql = "SELECT studentId, name, sex, age, grade, major, className, status, enrollDate, contactInfo " +
                "FROM student1 s " +
                "INNER JOIN student_course sc ON s.studentId = sc.StudentID " +
                "WHERE sc.ClassID = ?";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, classId);
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    String studentId = rs.getString("studentId");
                    Student student = new Student();
                    student.setStudentId(studentId);
                    student.setStudentName(rs.getString("name"));

                    AcademicRecord record = new AcademicRecord(
                            studentId,
                            rs.getString("sex"),
                            rs.getInt("age"),
                            rs.getString("grade"),
                            rs.getString("major"),
                            rs.getString("className"),
                            rs.getString("status"),
                            rs.getString("enrollDate"),
                            rs.getString("contactInfo")
                    );

                    student.setAcademicRecord(record);
                    students.add(student);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return students;
    }

    // -------------------------------
// 7️⃣ 根据教师ID获取该教师教授的课程及班级
// -------------------------------
    public List<Course> getCoursesByTeacher(String teacherId) {
        Map<String, Course> courseMap = new LinkedHashMap<>();
        String sql = "SELECT c.CourseID, c.CourseName, c.Credit, c.Department, " + // ✅
                "cl.ClassID, cl.TeacherID, cl.CourseTime, cl.CourseLocation, cl.Capacity, cl.SelectedCount, cl.ClassType, " +
                "t.teacherId, t.name, t.sex, t.age, t.department, t.contactInfo " +
                "FROM classroom cl " +
                "INNER JOIN course c ON cl.CourseID = c.CourseID " +
                "LEFT JOIN teacher1 t ON cl.TeacherID = t.teacherId " +
                "WHERE cl.TeacherID = ? " +
                "ORDER BY c.CourseID, cl.ClassID";


        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, teacherId);
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    String courseId = rs.getString("CourseID");
                    Course course = courseMap.get(courseId);
                    if (course == null) {
                        course = new Course(courseId, rs.getString("CourseName"), rs.getInt("Credit"), rs.getString("Department"));
                        courseMap.put(courseId, course);
                    }

                    String classId = rs.getString("ClassID");
                    if (classId != null) {
                        Classroom classroom = new Classroom();
                        classroom.setClassId(classId);
                        classroom.setCourseId(courseId);
                        classroom.setCourseTime(rs.getString("CourseTime"));
                        classroom.setCourseLocation(rs.getString("CourseLocation"));
                        classroom.setCapacity(rs.getInt("Capacity"));
                        classroom.setSelectedCount(rs.getInt("SelectedCount"));
                        classroom.setClassType(rs.getString("ClassType")); // ✅ 设置 ClassType

                        Teacher teacher = new Teacher();
                        teacher.setTeacherId(rs.getString("teacherId"));
                        teacher.setName(rs.getString("name"));
                        teacher.setSex(rs.getString("sex"));
                        teacher.setAge(rs.getInt("age"));
                        teacher.setDepartment(rs.getString("department"));
                        teacher.setContactInfo(rs.getString("contactInfo"));

                        classroom.setTeacher(teacher);
                        course.addClassroom(classroom);
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return new ArrayList<>(courseMap.values());
    }

    // -------------------------------
    // 管理员端功能
    // -------------------------------
    public boolean updateCourseById(String courseId, String newName, int newCredit, String newDepartment) {
        String sql = "UPDATE course SET CourseName=?, Credit=?, Department=? WHERE CourseId=?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, newName);
            ps.setInt(2, newCredit);
            ps.setString(3, newDepartment);
            ps.setString(4, courseId);
            return ps.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }


    public boolean updateCourseIdByName(String courseName, String newCourseId) {
        String sql = "UPDATE course SET CourseID=? WHERE CourseName=?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, newCourseId);
            ps.setString(2, courseName);
            return ps.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    public boolean addClassroom(Classroom classroom) {
        // 在SQL语句中添加ClassType字段
        String sql = "INSERT INTO classroom (ClassID, CourseID, TeacherID, CourseTime, CourseLocation, " +
                "Capacity, SelectedCount, ClassType) " +
                "VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, classroom.getClassId());
            ps.setString(2, classroom.getCourseId());
            ps.setString(3, classroom.getTeacher() != null ? classroom.getTeacher().getTeacherId() : null);
            ps.setString(4, classroom.getCourseTime());
            ps.setString(5, classroom.getCourseLocation());
            ps.setInt(6, classroom.getCapacity());
            ps.setInt(7, classroom.getSelectedCount());
            // 添加ClassType参数（假设Classroom类有getClassType()方法）
            ps.setString(8, classroom.getClassType());

            return ps.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    public boolean deleteClassroom(String classId) {
        String sql = "DELETE FROM classroom WHERE ClassID=?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, classId);
            return ps.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    public boolean updateClassroom(Classroom classroom) {
        // 在SQL语句中添加ClassType字段的更新
        String sql = "UPDATE classroom SET CourseTime=?, CourseLocation=?, Capacity=?, " +
                "TeacherID=?, ClassType=? WHERE ClassID=?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, classroom.getCourseTime());
            ps.setString(2, classroom.getCourseLocation());
            ps.setInt(3, classroom.getCapacity());
            ps.setString(4, classroom.getTeacher() != null ? classroom.getTeacher().getTeacherId() : null);
            // 添加ClassType参数设置
            ps.setString(5, classroom.getClassType());
            ps.setString(6, classroom.getClassId());

            return ps.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    public boolean addStudentToClass(String classId, String studentId) {
        String sql = "INSERT INTO student_course (StudentID, ClassID) VALUES (?, ?)";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, studentId);
            ps.setString(2, classId);
            return ps.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    public boolean removeStudentFromClass(String classId, String studentId) {
        String sql = "DELETE FROM student_course WHERE ClassID=? AND StudentID=?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, classId);
            ps.setString(2, studentId);
            return ps.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    public Course getCourseById(String courseId) {
        String sql = "SELECT CourseID, CourseName, Credit, Department FROM course WHERE CourseID = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, courseId);
            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    Course course = new Course();
                    course.setCourseId(rs.getString("CourseID"));
                    course.setCourseName(rs.getString("CourseName"));
                    course.setCredit(rs.getInt("Credit"));
                    course.setDepartment(rs.getString("Department")); // ⭐ 新增
                    return course;
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    // -------------------------------
// 8️⃣ 获取所有教师（管理员端）
// -------------------------------
    public List<Teacher> getAllTeachers() {
        List<Teacher> teachers = new ArrayList<>();
        String sql = "SELECT teacherId, name, sex, age, department, contactInfo FROM teacher1 ORDER BY teacherId";

        try (Connection conn = DBUtil.getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            while (rs.next()) {
                Teacher teacher = new Teacher();
                teacher.setTeacherId(rs.getString("teacherId"));
                teacher.setName(rs.getString("name"));
                teacher.setSex(rs.getString("sex"));
                teacher.setAge(rs.getInt("age"));
                teacher.setDepartment(rs.getString("department"));
                teacher.setContactInfo(rs.getString("contactInfo"));
                teachers.add(teacher);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return teachers;
    }

    public List<Course> getFirstTimeCourses(String studentId) {
        Map<String, Course> courseMap = new LinkedHashMap<>();
        String sql = "SELECT c.CourseID, c.CourseName, c.Credit, c.Department, " + // ⭐ 加上开课学院
                "cl.ClassID, cl.TeacherID, cl.CourseTime, cl.CourseLocation, "
                + "cl.Capacity, cl.SelectedCount, cl.ClassType, "
                + "t.teacherId, t.name, t.sex, t.age, t.department, t.contactInfo "
                + "FROM course c "
                + "JOIN classroom cl ON c.CourseID = cl.CourseID "
                + "JOIN teacher1 t ON cl.TeacherID = t.teacherId "
                + "WHERE cl.ClassType = '首修' "
                + "AND c.CourseID NOT IN ( "
                + "    SELECT s.courseId FROM score s WHERE s.studentId = ? "
                + ") "
                + "ORDER BY c.CourseID, cl.ClassID";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, studentId);
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    String courseId = rs.getString("CourseID");
                    Course course = courseMap.get(courseId);
                    if (course == null) {
                        course = new Course(courseId, rs.getString("CourseName"), rs.getInt("Credit"), rs.getString("Department"));
                        courseMap.put(courseId, course);
                    }

                    String classId = rs.getString("ClassID");
                    if (classId != null) {
                        Classroom classroom = new Classroom();
                        classroom.setClassId(classId);
                        classroom.setCourseId(courseId);
                        classroom.setCourseTime(rs.getString("CourseTime"));
                        classroom.setCourseLocation(rs.getString("CourseLocation"));
                        classroom.setCapacity(rs.getInt("Capacity"));
                        classroom.setSelectedCount(rs.getInt("SelectedCount"));
                        classroom.setClassType(rs.getString("ClassType"));

                        Teacher teacher = new Teacher();
                        teacher.setTeacherId(rs.getString("teacherId"));
                        teacher.setName(rs.getString("name"));
                        teacher.setSex(rs.getString("sex"));
                        teacher.setAge(rs.getInt("age"));
                        teacher.setDepartment(rs.getString("department"));
                        teacher.setContactInfo(rs.getString("contactInfo"));

                        classroom.setTeacher(teacher);
                        course.addClassroom(classroom);
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return new ArrayList<>(courseMap.values());
    }

    public List<Course> getRetakeCourses(String studentId) {
        Map<String, Course> courseMap = new LinkedHashMap<>();
        String sql = "SELECT c.CourseID, c.CourseName, c.Credit, c.Department, " + // ⭐ 加上开课学院
                "cl.ClassID, cl.TeacherID, cl.CourseTime, cl.CourseLocation, " +
                "cl.Capacity, cl.SelectedCount, cl.ClassType, " +
                "t.teacherId, t.name, t.sex, t.age, t.department, t.contactInfo " +
                "FROM course c " +
                "JOIN classroom cl ON c.CourseID = cl.CourseID " +
                "JOIN teacher1 t ON cl.TeacherID = t.teacherId " +
                "WHERE cl.ClassType = '重修' " +
                "AND c.CourseID IN ( " +
                "    SELECT s.courseId FROM score s WHERE s.studentId = ? AND s.score < 60 " +
                ") " +
                "ORDER BY c.CourseID, cl.ClassID";


        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, studentId);
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    String courseId = rs.getString("CourseID");
                    Course course = courseMap.get(courseId);
                    if (course == null) {
                        course = new Course(courseId, rs.getString("CourseName"), rs.getInt("Credit"), rs.getString("Department"));
                        courseMap.put(courseId, course);
                    }

                    String classId = rs.getString("ClassID");
                    if (classId != null) {
                        Classroom classroom = new Classroom();
                        classroom.setClassId(classId);
                        classroom.setCourseId(courseId);
                        classroom.setCourseTime(rs.getString("CourseTime"));
                        classroom.setCourseLocation(rs.getString("CourseLocation"));
                        classroom.setCapacity(rs.getInt("Capacity"));
                        classroom.setSelectedCount(rs.getInt("SelectedCount"));
                        classroom.setClassType(rs.getString("ClassType"));

                        Teacher teacher = new Teacher();
                        teacher.setTeacherId(rs.getString("teacherId"));
                        teacher.setName(rs.getString("name"));
                        teacher.setSex(rs.getString("sex"));
                        teacher.setAge(rs.getInt("age"));
                        teacher.setDepartment(rs.getString("department"));
                        teacher.setContactInfo(rs.getString("contactInfo"));

                        classroom.setTeacher(teacher);
                        course.addClassroom(classroom);
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return new ArrayList<>(courseMap.values());
    }

    public boolean hasTakenCourse(String studentId, String courseId) {
        String sql = "SELECT 1 FROM score WHERE studentId = ? AND courseId = ? LIMIT 1";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, studentId);
            ps.setString(2, courseId);
            try (ResultSet rs = ps.executeQuery()) {
                return rs.next(); // 查到记录就说明修过
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    public Integer getScore(String studentId, String courseId) {
        String sql = "SELECT score FROM score WHERE studentId = ? AND courseId = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, studentId);
            ps.setString(2, courseId);
            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    return rs.getInt("score");
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null; // 没有成绩记录
    }

    public boolean addCourse(Course course) {
        String sql = "INSERT INTO course (CourseID, CourseName, Credit, Department) VALUES (?, ?, ?, ?)";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, course.getCourseId());
            ps.setString(2, course.getCourseName());
            ps.setInt(3, course.getCredit());
            ps.setString(4, course.getDepartment());
            return ps.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    public boolean deleteCourse(String courseId) {
        String deleteStudentCourseSql = "DELETE FROM student_course WHERE ClassID IN (SELECT ClassID FROM classroom WHERE CourseID = ?)";
        String deleteClassroomSql = "DELETE FROM classroom WHERE CourseID = ?";
        String deleteScoreSql = "DELETE FROM score WHERE CourseID = ?";
        String deleteCourseSql = "DELETE FROM course WHERE CourseID = ?";

        try (Connection conn = DBUtil.getConnection()) {
            conn.setAutoCommit(false); // 开启事务

            // 1. 删除选课记录
            try (PreparedStatement ps = conn.prepareStatement(deleteStudentCourseSql)) {
                ps.setString(1, courseId);
                ps.executeUpdate();
            }

            // 2. 删除成绩记录
            try (PreparedStatement ps = conn.prepareStatement(deleteScoreSql)) {
                ps.setString(1, courseId);
                ps.executeUpdate();
            }

            // 3. 删除教学班
            try (PreparedStatement ps = conn.prepareStatement(deleteClassroomSql)) {
                ps.setString(1, courseId);
                ps.executeUpdate();
            }

            // 4. 删除课程
            try (PreparedStatement ps = conn.prepareStatement(deleteCourseSql)) {
                ps.setString(1, courseId);
                int affected = ps.executeUpdate();
                if (affected == 0) {
                    conn.rollback();
                    System.out.println("课程ID不存在: " + courseId);
                    return false;
                }
            }

            conn.commit(); // 提交事务
            return true;

        } catch (Exception e) {
            e.printStackTrace();
            try {
                DBUtil.getConnection().rollback(); // 回滚事务
            } catch (Exception ex) {
                ex.printStackTrace();
            }
        }
        return false;
    }

    public boolean insertStudentClassroomSafely(String studentId, String classId) {
        String updateCountSql = "UPDATE classroom SET SelectedCount = SelectedCount + 1 WHERE ClassID = ? AND SelectedCount < Capacity";
        String insertSql = "INSERT INTO student_course (StudentID, ClassID) VALUES (?, ?)";

        try (Connection conn = DBUtil.getConnection()) {
            conn.setAutoCommit(false);

            // 1️⃣ 原子更新人数
            int updatedRows;
            try (PreparedStatement ps = conn.prepareStatement(updateCountSql)) {
                ps.setString(1, classId);
                updatedRows = ps.executeUpdate();
            }

            if (updatedRows == 0) { // 容量已满
                conn.rollback();
                System.out.println("班级已满，选课失败: " + classId);
                return false;
            }

            // 2️⃣ 插入选课记录
            try (PreparedStatement ps = conn.prepareStatement(insertSql)) {
                ps.setString(1, studentId);
                ps.setString(2, classId);
                ps.executeUpdate();
            }

            conn.commit();
            return true;

        } catch (SQLException e) {
            e.printStackTrace();
            try {
                DBUtil.getConnection().rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
        return false;
}
}








